package edu.zju.udms.dnavigator.entities.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import edu.zju.udms.dnavigator.core.entity.Entity;
import edu.zju.udms.dnavigator.core.entity.ExtractorField;
import edu.zju.udms.dnavigator.core.entity.FieldTypeEnum;
import edu.zju.udms.dnavigator.core.entity.Record;
import edu.zju.udms.dnavigator.core.entity.Relation;
import edu.zju.udms.dnavigator.core.extract.Extractor;
import edu.zju.udms.dnavigator.core.utils.Tuple3;

public class MySQLExtractor extends Extractor{
	
	public static final String driverName = "com.mysql.jdbc.Driver";
	@ExtractorField(FieldTypeEnum.WEB_USE)
	public static final String SOURCE="MySQL";
	private static Logger logger = Logger.getLogger(MySQLExtractor.class);
	
	@ExtractorField(FieldTypeEnum.SAVE)
	@ExtractorField(FieldTypeEnum.WEB_USE)
	@ExtractorField(FieldTypeEnum.SHOW_IN_PANEL)
	public String host;
	
	@ExtractorField(FieldTypeEnum.SAVE)
	@ExtractorField(FieldTypeEnum.WEB_USE)
	@ExtractorField(FieldTypeEnum.SHOW_IN_PANEL)
	public String port="3306";
	
	@ExtractorField(FieldTypeEnum.SAVE)
	@ExtractorField(FieldTypeEnum.WEB_USE)
	public String username;
	
	@ExtractorField(FieldTypeEnum.SAVE)
	@ExtractorField(FieldTypeEnum.WEB_USE)
	public String password;
	
	@ExtractorField(FieldTypeEnum.SAVE)
	@ExtractorField(FieldTypeEnum.WEB_USE)
	@ExtractorField(FieldTypeEnum.SHOW_IN_PANEL)
	public String database;
	
	@Override
	public Tuple3<List<Entity>, List<Relation>, List<Record>> extract() throws Exception {
		List<Entity> entities=new ArrayList<>();
		Class.forName(driverName);
		String url=String.format("jdbc:mysql://%s:%s?user=%s&password=%s",
				host, port, username, password);
		logger.info("Mysql jdbc url : "+url);
		Connection conn = DriverManager.getConnection(url);
		Statement stmt = conn.createStatement();
		ResultSet res = stmt.executeQuery(String.format("select * from information_schema.schemata where schema_name = '%s'",database));
		String databaseId=null;
		String hostName=host+":"+port;
		Map<String,String> tableIdMap=new HashMap<>();
		if(res.next()){
			MySQLDatabase db = new MySQLDatabase(hostName,database);
			entities.add(db);
			databaseId=db.getId();
		}
		//table
		res = stmt.executeQuery(String.format("select * from information_schema.tables where table_schema = '%s'",database));
		while(res.next()){
			String name = res.getString("TABLE_NAME");
			String tableType = res.getString("TABLE_TYPE"); 
			long rows=res.getLong("TABLE_ROWS");
			Timestamp create_time=res.getTimestamp("CREATE_TIME");
			Timestamp update_time=res.getTimestamp("UPDATE_TIME");
			MySQLTable table = new MySQLTable(hostName,name, create_time!=null?create_time.getTime():0, update_time!=null?update_time.getTime():0, tableType, rows, database, databaseId);
			tableIdMap.put(name, table.getId());
			entities.add(table);
		}
		//column
		res = stmt.executeQuery(String.format("select * from information_schema.columns where table_schema = '%s'",database));
		while(res.next()) {
			String tableName = res.getString("TABLE_NAME");
			String name = res.getString("COLUMN_NAME"); 
			String dataType = res.getString("DATA_TYPE"); 
			entities.add(new MySQLColumn(database, tableName, name, tableIdMap.get(tableName), dataType));
		}
		conn.close();
		return new Tuple3<List<Entity>, List<Relation>, List<Record>>(entities, new ArrayList<>(), new ArrayList<>());
	}

	@Override
	public String getSource() {
		return SOURCE;
	}
	
}
